package com.example.project.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class MyDBHelper extends SQLiteOpenHelper{
	private static final String DB_NAME = "sql.db"; 	// 저장될 DB 이름
	private static final int 	DB_VER = 1;	// DB 버전
	private static MyDBHelper dbHelper;
	
	public MyDBHelper(Context context,  String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}
	
	public static MyDBHelper getInstance(Context context) {
		if (dbHelper == null) {
			dbHelper = new MyDBHelper(context, DB_NAME, null,
					DB_VER);
		}
		return dbHelper;
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		//테이블 생성 및 초기 작업 
		String sql = "CREATE TABLE historysql ("
				+ "_id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "list_no     CHAR(5)      NOT NULL,"				
				+ "UNIQUE(list_no))";

		db.execSQL(sql); 
		}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//DB의 버전이 바뀌면 수행
		db.execSQL("drop table if exist historysql");
		onCreate(db);
	}
	
	/*public Cursor selectCategory() {//카테고리 distinct로 추출
		// (distinct, table, columns, selection, selectionArgs, groupBy, having,
		// orderBy, limit)
		Cursor cursor = getWritableDatabase().query(true, "sql", new String[] { "list_no",
				"category" }, null, null, "list_no", null, null, null);
		return cursor;
	}*/

	public Cursor select() {//history list_no로 sql에 있는 카테고리 데려오기
		String sql = "SELECT distinct(s.list_no), s.category FROM sql s, historysql h WHERE s.list_no = h.list_no";
		Cursor cursor = getWritableDatabase().rawQuery(sql,null);
		return cursor;
	}
	
	public long insert(String list_no) {
		ContentValues values = new ContentValues();
		values.put("list_no", list_no);		
		//(table, nullColumnHack, values)
		long result = getWritableDatabase().insert("historysql", null, values);
		Log.d("print", result+"");
		return result;
	}
	
	public int delete(String list_no) {
		return getWritableDatabase().delete("historysql", "list_no=?", new String[] {list_no});
	}
}